// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace MsSqlSpatial
{
	public static class DatabaseHelper
	{
		// 1-item array used as workaround for a "writeable" static readonly field.
		private static readonly string[] contextConnection = new string[1];

		public static SqlConnection ContextConnection
		{
			get
			{
				return new SqlConnection(ContextConnectionString);
			}
		}

		/// <summary>
		/// Use this property for debugging/testing out of SQLCLR context.
		/// </summary>
		public static string ContextConnectionString
		{
			get
			{
				if (SqlContext.IsAvailable)
					return "context connection=true";
				else
					return contextConnection[0];
			}
			set
			{
				contextConnection[0] = value;
			}
		}

		internal static bool CheckQueryExists(SqlCommand command, string query, params object[] parameters)
		{
			try
			{
				BuildCommandArray(command, query, parameters);
				object ret = command.ExecuteScalar();
				return (ret != null);
			}
			catch
			{
				return false;
			}
		}
	
		internal static bool CheckMetaTableExists(SqlCommand command, string tableName)
		{
			return CheckQueryExists(command, string.Format(
	@"SELECT [name] FROM [sys].[objects] WHERE [object_id] = OBJECT_ID(N'[ST].[{0}]') AND [type] = N'U'"
	, tableName));
		}

		internal static bool CheckTriggerExists(SqlCommand command, string schemaName, string triggerName)
		{
			return CheckQueryExists(command, 
@"SELECT t.object_id
FROM sys.triggers t, sys.objects o, sys.schemas s
WHERE t.object_id = o.object_id
AND o.schema_id = s.schema_id
AND s.name = @p0
AND t.name = @p1"
, schemaName
, triggerName
);
		}

		internal static bool CheckColumnExists(SqlCommand command, string schemaName, string tableName, string columnName)
		{
			return CheckQueryExists(command, string.Format(
@"SELECT object_id
FROM sys.columns
WHERE object_id = OBJECT_ID(N'[{0}].[{1}]')
AND name = @p0"
, schemaName
, tableName
)
, columnName
);
		}

		internal static void DropColumn(SqlCommand command, string schemaName, string tableName, string columnName)
		{
			if (DatabaseHelper.CheckColumnExists(command, schemaName, tableName, columnName))
			{
				DatabaseHelper.BuildCommand(command, string.Format(
@"ALTER TABLE [{0}].[{1}]
DROP COLUMN [{2}]
"
, schemaName, tableName, columnName));
				command.ExecuteNonQuery();
			}
		}


		internal static void DropIndex(SqlCommand command, string schemaName, string tableName, string indexName)
		{
			if (DatabaseHelper.CheckIndexExists(command, schemaName, tableName, indexName))
			{
				DatabaseHelper.BuildCommand(command, string.Format(
@"DROP INDEX [{2}] 
ON [{0}].[{1}]
"
, schemaName, tableName, indexName));
				command.ExecuteNonQuery();
			}
		}

		internal static bool CheckIndexExists(SqlCommand command, string schemaName, string tableName, string indexName)
		{
			return CheckQueryExists(command, string.Format(
@"
SELECT object_id
FROM sys.indexes 
WHERE object_id = OBJECT_ID(N'[{0}].[{1}]')
AND name = @p0"
, schemaName
, tableName
)
, indexName
);
		}

		internal static bool CheckConstraintExists(SqlCommand command, string schemaName, string tableName, string constraintName)
		{
			return CheckQueryExists(command, string.Format(
@"SELECT object_id
FROM sys.check_constraints 
WHERE object_id = OBJECT_ID(N'[{0}].[{2}]')
AND parent_object_id = OBJECT_ID(N'[{0}].[{1}]')"
, schemaName
, tableName
, constraintName
));
		}

		internal static string CheckSchemaName(SqlCommand command, SqlString schemaName)
		{
			return CheckSchemaName(command,schemaName.ToString());
		}

		internal static string CheckSchemaName(SqlCommand command, string schemaName)
		{
			string checkedSchema = "dbo";
			if (schemaName.Trim().Length != 0)
			{
				bool schemaExists = CheckQueryExists(command, 
	@"SELECT [name] FROM [sys].[schemas] WHERE [name] = @p0"
	, schemaName);
	
				if (schemaExists)
				{
					checkedSchema = schemaName;
				}
			}
			return checkedSchema;
		}
	
		internal static void ExecuteDropTable(SqlCommand command, string schemaName, string tableName)
		{
			DatabaseHelper.BuildCommand(command, string.Format(
	@"DROP TABLE [{0}].[{1}]"
	, schemaName, tableName));
			command.ExecuteNonQuery();
		}

		internal static void SplitSchemaAndTableName(SqlCommand command, ref string tableName, ref string schemaName)
		{
			int schemaDot = tableName.IndexOf(".");
			if (schemaDot != -1)
			{
				schemaName = tableName.Substring(0, schemaDot);
				tableName = tableName.Substring(schemaDot + 1);
			}
			schemaName = CheckSchemaName(command, schemaName);
		}

		internal static void BuildCommand(SqlCommand command, string commandText, params object[] parameters)
		{
			BuildCommandArray(command, commandText, parameters);
		}

		internal static void BuildCommandArray(SqlCommand command, string commandText, object[] parameters)
		{
			command.CommandText = commandText;
			command.CommandType = CommandType.Text;
			command.Parameters.Clear();
			for (int i = 0; i < parameters.Length; i++)
			{
				command.Parameters.AddWithValue("@p" + i.ToString(), parameters[i]);
			}
		}

	}
}
